package com.cobo.fitness.web.calibreapi.services;

import cn.hutool.core.collection.CollectionUtil;
import com.cobo.fitness.core.Opf;
import com.cobo.fitness.core.OpfUtils;
import com.cobo.fitness.web.calibreapi.entity.Book;
import com.cobo.fitness.web.calibreapi.entity.CalibreBook;
import com.cobo.fitness.web.calibreapi.entity.Library;
import com.cobo.fitness.web.calibreapi.model.BookMeta;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.filefilter.SuffixFileFilter;
import org.apache.commons.io.filefilter.TrueFileFilter;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

public class CalibreApi {

    private static final String DB_URL = "jdbc:sqlite:C:/Users/Lame/Calibre 书库/metadata.db";


    // Database connection method
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL);
    }

    // Create (Insert) a new book
    public static void insertBook(String title, String author) {
        String sql = "INSERT INTO books (title, author) VALUES (?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, title);
            pstmt.setString(2, author);
            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    // Read (Select) books
    public static List<CalibreBook> selectBooks() {
        String sql = "SELECT * FROM books";
        List<CalibreBook> books = new ArrayList<>();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                // Retrieve by column name
                int id = rs.getInt("id");
                String title = rs.getString("title");
                String sort = rs.getString("sort");
                Date timestamp = rs.getDate("timestamp");
//                Timestamp pubdate1 = rs.getTimestamp("pubdate");
                long pubdate = rs.getLong("pubdate");
                int seriesIndex = rs.getInt("series_index");
                String authorSort = rs.getString("author_sort");
                String isbn = rs.getString("isbn");
//                String iccn = rs.getString("iccn");
                String path = rs.getString("path");
                int flags = rs.getInt("flags");
                String uuid = rs.getString("uuid");
                int hasCover = rs.getInt("has_cover");
                Date lastModified = rs.getDate("last_modified");

                CalibreBook buildBook = CalibreBook.builder()
                        .id(id)
                        .title(title)
                        .sort(sort)
                        .timestamp(timestamp)
                        .pubdate(pubdate)
                        .seriesIndex(seriesIndex)
                        .authorSort(authorSort)
                        .isbn(isbn)
//                        .iccn(iccn)
                        .path(path)
                        .flags(flags)
                        .uuid(uuid)
                        .hasCover(hasCover)
                        .lastModified(lastModified)
                        .build();


                books.add(buildBook);

            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return books;
    }

    // Update a book's information
    public static void updateBook(int id, String newTitle) {
        String sql = "UPDATE books SET title = ? WHERE id = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, newTitle);
            pstmt.setInt(2, id);
            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    // Delete a book
    public static void deleteBook(int id) {
        String sql = "DELETE FROM books WHERE id = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    private static List<String> mapTolist(List<Book> books, Function<Book, String> mapper) {
        return books.stream().map(mapper).filter(Objects::nonNull).distinct().collect(Collectors.toList());
    }

    public static BookMeta getBookMeta(List<Book> books) {
        if (books == null) {
            return null;
        }
        return BookMeta.builder()
                .author(mapTolist(books,Book::getAuthor))
                .bookTag(mapTolist(books,Book::getBookTag))
                .bookLang(mapTolist(books,Book::getBookLang))
                .series(mapTolist(books,Book::getSeries))
                .bookFormat(mapTolist(books,Book::getBookFormat))
                .bookRate(mapTolist(books,Book::getBookRate))
                .bookPublish(mapTolist(books,Book::getBookPublish))
                .tagCode(mapTolist(books,Book::getTagCode))
                .books(books)
                .build();
    }

    public static List<Book> initLibrary(String path) {

        Collection<File> pdf = FileUtils.listFiles(new File(path), new SuffixFileFilter("pdf"), TrueFileFilter.TRUE);
        if (!CollectionUtils.isEmpty(pdf)) {
            return pdf.stream().map(it -> {
                Book book = new Book();
                book.setName(it.getName());
                book.setAuthor(it.getParentFile().getParentFile().getName());
                book.setBookSize( String.format("%.2f", (it.length() / 1024.0) / 1024));
                Opf opf = OpfUtils.invoke(new File(it.getParentFile().getAbsolutePath(), "metadata.opf").getAbsolutePath());
                if (opf != null) {
                    book.setBookLang(opf.getLanguage());
                    book.setName(opf.getTitle());
                }
                return book;
            }).collect(Collectors.toList());
        }
        return new ArrayList<>(0);
    }

    public static List<String> getBookMeta() {
        return Arrays.asList("author", "bookLang", "series", "bookFormat", "bookPublish", "bookRate", "bookTag", "tagCode");
    }
}
